﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WPCAutomatic.DAL
{
    internal class ThresholdScheme
    {
        private  readonly string _connectionString;
        public ThresholdScheme(string connectionString)
        {
            _connectionString = connectionString; //将接收到的字符串信息赋值
        }
        /// <summary>
        /// 获取所有表名
        /// </summary>
        public List<string> GetAllTableNames()
        {
            var tableNames = new List<string>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    string sql = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tableNames.Add(reader["name"].ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("获取表名失败", ex);
            }
            return tableNames;
        }
        /// <summary>
        /// 从指定表加载数据
        /// </summary>
        public List<ThresholdData> LoadDataFromTable(string tableName)
        {
            var dataList = new List<ThresholdData>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    string sql = $"SELECT [校准对象], [误差上限], [误差下限], [单位] FROM [{tableName}] ORDER BY ID";
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                dataList.Add(new ThresholdData
                                {
                                    CalibrationObject = reader["校准对象"].ToString(),
                                    UpperLimit = reader["误差上限"].ToString(),
                                    LowerLimit = reader["误差下限"].ToString(),
                                    Unit = reader["单位"].ToString()
                                });
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"从表 {tableName} 加载数据失败", ex);
            }
            return dataList;
        }
        /// <summary>
        /// 删除指定表
        /// </summary>
        public void DeleteTable(string tableName)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    string dropTableSql = $"DROP TABLE IF EXISTS [{tableName}]";
                    using (var dropTableCmd = new SQLiteCommand(dropTableSql, connection))
                    {
                        dropTableCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"删除表 {tableName} 失败", ex);
            }
        }
        /// <summary>
        /// 保存方案到数据库
        /// </summary>
        public void SaveScheme(string schemeName, List<ThresholdData> dataList)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    // 创建新表
                    string createTableSql = $@"      
CREATE TABLE [{schemeName}] (
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT,
    [校准对象] TEXT NOT NULL,
    [误差上限] TEXT,
    [误差下限] TEXT,
    [单位] TEXT      
)";
                    using (var createTableCmd = new SQLiteCommand(createTableSql, connection))
                    {
                        createTableCmd.ExecuteNonQuery();
                    }
                    // 插入数据
                    foreach (var data in dataList)
                    {
                        string insertSql = $@"      
INSERT INTO [{schemeName}] ([校准对象], [误差上限], [误差下限], [单位])      
VALUES (@校准对象, @误差上限, @误差下限, @单位)";
                        using (var insertCmd = new SQLiteCommand(insertSql, connection))
                        {
                            insertCmd.Parameters.AddWithValue("@校准对象", data.CalibrationObject ?? "");
                            insertCmd.Parameters.AddWithValue("@误差上限", data.UpperLimit ?? "");
                            insertCmd.Parameters.AddWithValue("@误差下限", data.LowerLimit ?? "");
                            insertCmd.Parameters.AddWithValue("@单位", data.Unit ?? "");
                            insertCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"保存方案 {schemeName} 失败", ex);
            }
        }
    }
    /// <summary>
    /// 门限数据实体类
    /// </summary>
    public class ThresholdData
    {
        public string CalibrationObject { get; set; }
        public string UpperLimit { get; set; }
        public string LowerLimit { get; set; }
        public string Unit { get; set; }
    }
}

